#!/usr/bin/perl -w

use DBI;
use strict;
use CGI qw(:standard);

my $db="jctong";
my $host="ilwrath";
my $user="nbuadmin";
my $password="asdfjk";

my $dbh = DBI->connect ("DBI:mysql:database=$db:host=$host",$user,$password) or die "Can't connect to database: $DBI::errstr\n";

my $sth;

my $lv=$dbh->prepare("select id,last_verified,substring_index(class,' ',1),substring_index(client,' ',1),include from policy_audit where substring_index(substring_index(info,' ',11),' ',-1) = 0 AND substring_index(class,' ',1)=? AND last_verified != 0 order by id desc"); 

my $update=$dbh->prepare("update policy_audit set last_verified=now() where id=?"); 

print header,start_html;

my $client;

if(param()){
	foreach my $key (param()){	
		if($key=~/id_(\d+)/){
			my $value=param($key);
			print "$key updated<br><br>\n";
			$update->execute($1);
		}
		if($key=~/client/){
			$client=param('client');
			if($client eq 'all'){
				$sth=$dbh->prepare("select id,last_verified,substring_index(class,' ',1),substring_index(client,' ',1),include from policy_audit where substring_index(substring_index(info,' ',11),' ',-1) = 0 AND timestamp = (select max(timestamp) from policy_audit)");
				$sth->execute();
			}else{
				$sth=$dbh->prepare("select id,last_verified,substring_index(class,' ',1),substring_index(client,' ',1),include from policy_audit where substring_index(substring_index(info,' ',11),' ',-1) = 0 AND substring_index(client,' ',1)=? AND timestamp = (select max(timestamp) from policy_audit)");
				$sth->execute($client);
			}
		}
	}
}

print <<END;
Verify backup policies.
<br>
<form action="pa_activepolicies.pl" method="GET">
Enter a client name: <input type="text" name="client" value="$client">
<br>
Click on checkbox to confirm accuracy. Then click Submit when finished.
<br>
<input type="submit">
<hr>
END
;

print "<table border='1'>";

while(my($id,$last_verified,$class,$client,$include)=$sth->fetchrow_array()){
	my $param_id="id_".$id;
	print "<tr><td bgcolor='blue'><input type='checkbox' name=\'$param_id\'></td>";
	if($last_verified=~/0000-00-00/){
		print "<td bgcolor='yellow'>";
	}else{
		print "<td bgcolor='#99FF66'>";
	}
	print "id: $id<br>";
	print "client: $client<br>";
	print "policy name: $class<br>";
	print "path(s): $include<br>";
	print "last verified: $last_verified</td>";

	$lv->execute($class);	
	while(my($id,$last_verified,$class,$client,$include)=$lv->fetchrow_array()){
		my $param_id="id_".$id;
		print "<td>";
		print "id: $id<br>";
		print "client: $client<br>";
		print "policy name: $class<br>";
		print "path(s): $include<br>";
		print "last verified: $last_verified<br>";
		print "</td>";
	}
	print "</tr>";
}

print "</form>";
print end_html;
